IF OBJECT_ID('dbo.GetGroupByCodes') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetGroupByCodes
    IF OBJECT_ID('dbo.GetGroupByCodes') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetGroupByCodes >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetGroupByCodes >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetGroupByCodes
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/6/2007
--
-- Description: Returns list of Group by Codes by Report...
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetGroupByCodes('TYPE')
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/6/07      KC        Created
-- 8/15/07     KC        Order By
-- 8/17/07     KC        Added AgreementTermination
-- 8/27/07     KC        Added 'PostAwardManager' to SubIn & Out Reports
-- 9/18/07     KC        Added 'PreAwardManager' to Advance Rpt.
-- 9/28/07     KC        Added some Columns after QA feedback.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetGroupByCodes
     (
     @Report      AS   VARCHAR(100)
     )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     FieldName           VARCHAR             
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     IF @Report = 'AgreementDeliverables'
     BEGIN
          SELECT    'PostAwardManager' AS FieldName
          UNION
          SELECT    'PreAwardManager' AS FieldName
          UNION
          SELECT    'PrincipalInvestigator' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END  
     
     IF @Report IN ('AdvancedAccountsByDeptReport','AwardingReport','AwardsBookedReport')
     BEGIN
          SELECT    'Sponsor' AS FieldName
          UNION
          SELECT    'Institution' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          UNION
          SELECT    'PostAwardManager' AS FieldName
          UNION
          SELECT    'PreAwardManager' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END     

     IF @Report = 'IndustryAgreementReceivedReport'
     BEGIN
          SELECT    'Institution' AS FieldName
          UNION
          SELECT    'PrincipalInvestigator' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          UNION
          SELECT    'CSCRCaseManager' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END     

     IF @Report = 'ProposalTransactionReport'
     BEGIN
          SELECT    'Sponsor' AS FieldName
          UNION
          SELECT    'Institution' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          UNION
          SELECT    'PostAwardManager' AS FieldName
          UNION
          SELECT    'ProposalStatus' AS FieldName
          UNION
          SELECT    'PreAwardManager' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END     

     IF @Report = 'SubcontractorsInReport'
     BEGIN
          SELECT    'Sponsor' AS FieldName
          UNION
          SELECT    'Institution' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          UNION
          SELECT    'BudgetStatus' AS FieldName
          UNION
          SELECT    'PostAwardManager' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END     
     
     IF @Report = 'SubcontractorsOutReport'
     BEGIN
          SELECT    'Sponsor' AS FieldName
          UNION
          SELECT    'Institution' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          UNION
          SELECT    'BudgetStatus' AS FieldName
          UNION
          SELECT    'Subcontractor' AS FieldName
          UNION
          SELECT    'PreAwardManager' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END     

     IF @Report = 'AgreementTerminationReport'
     BEGIN
          SELECT    'PostAwardManager' AS FieldName
          UNION
          SELECT    'Department' AS FieldName
          UNION
          SELECT    'Institution' AS FieldName
          UNION
          SELECT    'PreAwardManager' AS FieldName
          UNION
          SELECT    'PrincipalInvestigator' AS FieldName
          ORDER BY FieldName
          -- Return Success
          RETURN 0
     END     

     -- Default if we got here:
     SELECT 'Error: ReportNotSetup' AS FieldName
     
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetGroupByCodes') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetGroupByCodes >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetGroupByCodes >>>'
go

GRANT EXECUTE ON dbo.GetGroupByCodes TO InsightReport,Insight
go
